using System;
using System.Data;
using System.Data.SQLite;
using Model;

namespace DAL {
    public class MemberDAL {
        private MemberDAL() { }
        private static MemberDAL _memberDal = new MemberDAL();

        public static MemberDAL GetInstance() {
            return _memberDal;
        }
        public DataTable SelectAllMember(bool isDeleted) {
            SQLiteCommand command =
                new SQLiteCommand(
                    "select mid,mname,gender,type,discount,money,points,birthday,phonenum,registdate,enddate from memberinfo where removeflag = @isDeleted");

            if (isDeleted) {
                command.Parameters.AddWithValue("@isDeleted", 1);
            } else {
                command.Parameters.AddWithValue("@isDeleted", 0);
            }

            return SqlHelper.GetDataTable(command);
        }

        public int CountPage(int pageSize, bool isDeleted) {
            SQLiteCommand command = new SQLiteCommand("select count(*) from memberinfo where removeflag = @isDeleted");

            if (isDeleted) {
                command.Parameters.AddWithValue("@isDeleted", 1);
            } else {
                command.Parameters.AddWithValue("@isDeleted", 0);
            }

            return int.Parse(SqlHelper.GetSingleResult(command).ToString());
        }

        public DataTable Paging(int pageNumber, int pageSize , bool isDeleted) {
            SQLiteCommand command = new SQLiteCommand("select mid,mname,gender,type,discount,money,points,birthday,phonenum,registdate,enddate from memberinfo where removeflag = @isDeleted limit @start, @pageSize");
            if (isDeleted) {
                command.Parameters.AddWithValue("@isDeleted", 1);
            } else {
                command.Parameters.AddWithValue("@isDeleted", 0);
            }
            command.Parameters.AddWithValue("@start", (pageNumber - 1) * pageSize);
            command.Parameters.AddWithValue("@pageSize", pageSize);
            return SqlHelper.GetDataTable(command);
        }

        public int InsertMember(Member member) {
            string sql = "INSERT INTO MemberInfo(mname, gender, type, discount, money, points, birthday, phoneNum, registDate, endDate) " +
                         "VALUES (@mname, @gender, @type, @discount, @money, @points, @birthday, @phoneNum, @registDate, @endDate);";
            SQLiteParameter[] parameters = {
                new SQLiteParameter("@mname", member.Mname),
                new SQLiteParameter("@gender", member.Gender),
                new SQLiteParameter("@type", member.Type),
                new SQLiteParameter("@discount", member.Discount),
                new SQLiteParameter("@money", member.Money),
                new SQLiteParameter("@points", member.Points),
                new SQLiteParameter("@birthday", member.Birthday),
                new SQLiteParameter("@phoneNum", member.PhoneNum),
                new SQLiteParameter("@registDate", member.RegistDate),
                new SQLiteParameter("@endDate", member.EndDate),
            };
            SQLiteCommand command = new SQLiteCommand(sql);
            command.Parameters.AddRange(parameters);
            return SqlHelper.Update(command);
        }

        public DataTable SelectMemberType() {
            return SqlHelper.GetDataTable(new SQLiteCommand("select tid,tname from membertype"));
        }

        public int UpdateMember(Member member) {
            SQLiteCommand command = new SQLiteCommand(
                "UPDATE MemberInfo SET mname = @mname, gender = @gender, type = @type," +
                "discount = @discount, money = @money,  points = @points,  birthday = @birthday, phoneNum = @phoneNum," +
                " registDate = @registDate, endDate = @endDate WHERE mid = @mid");
            SQLiteParameter[] parameters = {
                new SQLiteParameter("@mid", member.Mid),
                new SQLiteParameter("@mname", member.Mname),
                new SQLiteParameter("@gender", member.Gender),
                new SQLiteParameter("@type", member.Type),
                new SQLiteParameter("@discount", member.Discount),
                new SQLiteParameter("@money", member.Money),
                new SQLiteParameter("@points", member.Points),
                new SQLiteParameter("@birthday", member.Birthday),
                new SQLiteParameter("@phoneNum", member.PhoneNum),
                new SQLiteParameter("@registDate", member.RegistDate),
                new SQLiteParameter("@endDate", member.EndDate),
            };
            command.Parameters.AddRange(parameters);
            return SqlHelper.Update(command);
        }

        public int TransMemberRemoveFlag(Member member, bool isDeleted) {
            SQLiteCommand command = new SQLiteCommand("update memberinfo set removeFlag = @isDeleted where mid=@mid");
            command.Parameters.AddWithValue("@mid", member.Mid);
            if (isDeleted) {
                command.Parameters.AddWithValue("@isDeleted", 1);
            } else {
                command.Parameters.AddWithValue("@isDeleted", 0);
            }
            return SqlHelper.Update(command);
        }

        public DataTable SelectByID(int mid) {
            SQLiteCommand command = new SQLiteCommand("select mid,mname,gender,type,discount,money,points,birthday,phonenum,registdate,enddate from memberinfo where mid=@mid");
            command.Parameters.AddWithValue("@mid", mid);
            return SqlHelper.GetDataTable(command);
        }

        public DataTable SelectByName(string queryStr) {
            SQLiteCommand command = new SQLiteCommand("select mid,mname,gender,type,discount,money,points,birthday,phonenum,registdate,enddate from memberinfo where mname like @queryStr");
            command.Parameters.AddWithValue("@queryStr","%" + queryStr + "%");
            return SqlHelper.GetDataTable(command);
        }

        public Member SelectByPhone(string phone) {
            SQLiteDataReader sqLiteDataReader = SqlHelper.GetDataReader(new SQLiteCommand("select * from memberinfo where phoneNum like @phone") {
                Parameters = { new SQLiteParameter("@phone","_______" + phone) }
            });
            Member member = null;
            while (sqLiteDataReader.Read()) {
                member = new Member {
                    Mid = int.Parse(sqLiteDataReader["mid"].ToString()),
                    Mname = sqLiteDataReader["mname"].ToString(),
                    Gender = sqLiteDataReader["gender"].ToString(),
                    Type = int.Parse(sqLiteDataReader["type"].ToString()),
                    Discount = int.Parse(sqLiteDataReader["discount"].ToString()),
                    Money = int.Parse(sqLiteDataReader["money"].ToString()),
                    Points = int.Parse(sqLiteDataReader["points"].ToString()),
                    Birthday = DateTime.Parse(sqLiteDataReader["birthday"].ToString()),
                    PhoneNum = sqLiteDataReader["phoneNum"].ToString(),
                    RegistDate = DateTime.Parse(sqLiteDataReader["registDate"].ToString()),
                    EndDate = DateTime.Parse(sqLiteDataReader["endDate"].ToString())
                };
            }
            sqLiteDataReader.Close();
            return member;
        }
    }
}